package com.dalei.vdserver.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Date;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.dalei.vdserver.model.DeviceStatus;

public class DeviceStatusDaoImpl extends JdbcDaoSupport implements
		DeviceStatusDao {

	@Override
	@SuppressWarnings("unchecked")
	public List<DeviceStatus> getStatusOfAllDevices() {
		// TODO Auto-generated method stub
		final String sql = "SELECT * FROM vds_device_status";
		List<DeviceStatus> dss = null;
		try {
			dss = getJdbcTemplate().query(sql, new RowMapper() {

				@Override
				public Object mapRow(ResultSet rs, int rowNum)
						throws SQLException {
					// TODO Auto-generated method stub
					DeviceStatus ds = new DeviceStatus();
					ds.setDeviceId(rs.getInt("_deviceId"));
					ds.setCurrentUserId(rs.getInt("_currentUserId"));
					Timestamp timestamp = rs.getTimestamp("_expiredTime");
					Date date = new Date(timestamp.getTime());
					ds.setExpiredTime(date);
					return ds;
				}
			});
		} catch (DataAccessException e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
		return dss;
	}

	@Override
	@SuppressWarnings("unchecked")
	public List<DeviceStatus> getStatusOfOnlineDevices() {
		// TODO Auto-generated method stub
		final String sql = "SELECT * FROM vds_device_status "
				+ "WHERE _expiredTime > GETDATE()";
		List<DeviceStatus> dss = null;
		try {
			dss = getJdbcTemplate().query(sql, new RowMapper() {

				@Override
				public Object mapRow(ResultSet rs, int rowNum)
						throws SQLException {
					// TODO Auto-generated method stub
					DeviceStatus ds = new DeviceStatus();
					ds.setId(rs.getInt("_id"));
					ds.setDeviceId(rs.getInt("_deviceId"));
					ds.setCurrentUserId(rs.getInt("_currentUserId"));
					Timestamp timestamp = rs.getTimestamp("_expiredTime");
					Date date = new Date(timestamp.getTime());
					ds.setExpiredTime(date);
					return ds;
				}
			});
		} catch (DataAccessException e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
		return dss;
	}

	@Override
	public DeviceStatus getStatusByDeviceId(int deviceId) {
		// TODO Auto-generated method stub
		final String sql = "SELECT * FROM vds_device_status " + "WHERE _deviceId = ?";
		DeviceStatus deviceStatus = null;
		try {
			deviceStatus = (DeviceStatus) getJdbcTemplate().queryForObject(sql,
					new Object[] { deviceId }, new RowMapper() {

						@Override
						public Object mapRow(ResultSet rs, int rowNum)
								throws SQLException {
							// TODO Auto-generated method stub
							DeviceStatus ds = new DeviceStatus();
							ds.setId(rs.getInt("_id"));
							ds.setDeviceId(rs.getInt("_deviceId"));
							ds.setCurrentUserId(rs.getInt("_currentUserId"));
							Timestamp timestamp = rs
									.getTimestamp("_expiredTime");
							Date date = new Date(timestamp.getTime());
							ds.setExpiredTime(date);
							return ds;
						}
					});
		} catch (IncorrectResultSizeDataAccessException e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		} catch (DataAccessException e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
		return deviceStatus;
	}

	@Override
	public int addStatus(final DeviceStatus status) {
		// TODO Auto-generated method stub
		final String sql = "INSERT INTO vds_device_status (_deviceId, _currentUserId, _expiredTime )" +
				"VALUES (?, ?, ?)";
		int rowAffected = 0;
		try {
			rowAffected = getJdbcTemplate().update(sql,
					new PreparedStatementSetter() {

						@Override
						public void setValues(PreparedStatement ps)
								throws SQLException {
							// TODO Auto-generated method stub
							ps.setInt(1, status.getDeviceId());
							if (status.getDeviceId() != 0) {
								ps.setInt(2, status.getCurrentUserId());
							} else {
								ps.setNull(2, Types.INTEGER);
							}
							Date date = status.getExpiredTime();
							Timestamp timestamp = new Timestamp(date.getTime());
							ps.setTimestamp(3, timestamp);
						}
					});
		} catch (DataAccessException e) {
			// TODO: handle exception
			e.printStackTrace();
			return -1;
		}
		return rowAffected;
	}

	@Override
	public int updateStatus(final DeviceStatus status) {
		// TODO Auto-generated method stub
		final String sql = "UPDATE vds_device_status SET _currentUserId = ?, _expiredTime = ? "
				+ "WHERE _deviceId = ?";
		int rowAffected = 0;
		try {
			rowAffected = getJdbcTemplate().update(sql,
					new PreparedStatementSetter() {

						@Override
						public void setValues(PreparedStatement ps)
								throws SQLException {
							// TODO Auto-generated method stub
							ps.setInt(1, status.getCurrentUserId());
							Date date = status.getExpiredTime();
							if (date == null) {
								ps.setTimestamp(2, null);
							} else {
								Timestamp timestamp = new Timestamp(date
										.getTime());
								ps.setTimestamp(2, timestamp);
							}
							ps.setInt(3, status.getDeviceId());
						}
					});
		} catch (DataAccessException e) {
			// TODO: handle exception
			e.printStackTrace();
			return -1;
		}
		return rowAffected;
	}
}
